/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Li Wen, 2007-12-18
 * @Version: 071231.003
 * @Change: Li Wen, 2008-03-21
 * Data access object of Departure model
 */
package com.ibsol.database
{
	import com.ibsol.model.Departure;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	public class DepartureDAO
	{
		//Database connection
		private var _tourixDBConn:SQLConnection;
		/**
		 * Departure data constructor, gets tourix database connection
		 * @param: null
		 */
		public function DepartureDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		/**
		 * Loads departure days according to package id
		 * @param:	id:int						package id
		 * 			resultHandler:Function		result function adapter, return array collection of departure
		 * 			faultHandler:Function		fault function adapter
		 * @return:	void
		 */
		public function loadDeparturesByPackage(id:int, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM departure WHERE packageid=" + id + " AND offlineOperator !='DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Load price according to package id
		 * @param:	companyid:int				公司ID
		 * 			packageid:int				旅游线路ID
		 * 			startdate:string			起日期
		 * 			enddate:string				止日期
		 * 			resultHandler:Function	result function adapter, 返回某一旅游线路起止日期间的记录
		 * 			faultHandler:Function	fault funcition adapter
		 */				
		public function loadDeparture(companyid:int, packageid:int, startdate:String, enddate:String, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = " select a.id, a.packageid, a.departureYear, a.departureMonth, a.departureDate, a.slot, a.available, a.updateTime, a.offlineOperator, " + 
					" (cast(departureYear as text)||'-'" + 
					"||case when departureMonth <10 then '0' else '' end||cast(departureMonth as text)||'-'" + 
					"||case when departureDate <10 then '0' else '' end||cast(departureDate as text)) as date " + 
					" from departure a, package b " + 
					" where a.packageid = b.id " +
					" and packageid = " + packageid + 
					" and date >= '" + startdate + "'" +
					" and date <= '" + enddate + "'" + 
					" and a.offlineOperator != 'DEL' and b.offlineOperator != 'DEL' " + 
					" order by departureYear, departureMonth, departureDate ";
			trace (sqlStr.text);
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		    			
		}
		/**
		 * Creates a new departure into database
		 * departure (id, packageid, departureDate, departureMonth, departureYear, slot, available, updateTime, offlineOperator)
		 * @param:	dpObj:Departure		new departure object, will be inserted into database
		 */
		public function insertDeparture(dpObj:Departure, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO departure (packageid, departureDate, departureMonth, departureYear, slot, available, updateTime, offlineOperator) " + 
					"values (" + dpObj.packageid + "," + dpObj.departureDate + "," + dpObj.departureMonth + "," + dpObj.departureYear + "," + dpObj.slot + "," + 
					dpObj.available + ",'T','NEW')";
			sqlStr.addEventListener(SQLEvent.RESULT, function (event:SQLEvent):void
			{
				dpObj.id = sqlStr.getResult().lastInsertRowID;
				if(resultHandler != null)
					resultHandler.call(this, dpObj.id);
			});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Update a departure date
		 */
		public function updateDeparture(dpObj:Departure, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE departure SET departureYear = " + dpObj.departureYear + ", departureMonth = " + dpObj.departureMonth + ", departureDate = " + 
					dpObj.departureDate + ", slot = " + dpObj.slot + ", available = " + dpObj.available + ", updateTime = 'T', offlineOperator = 'NEW' " + 
							"WHERE id = " + dpObj.id;
			sqlStr.addEventListener(SQLEvent.RESULT, function(event:SQLEvent):void
			{
				if(resultHandler != null)
					resultHandler.call(this);
			});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Deletes departure dates offline, just sets the departure table field offlineOperator to "DEL".
		 * @param:	id:int					package id, whose departures dates are deleted
		 *			resultHandler:Function	result handler function
		 *			faultHandler:Function	fault handler function
		 * @return: void
		 */
		public function deleteDepartureByPackage(packageid:int, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE departure SET offlineOperator='DEL', updateTime = 'T' WHERE packageid=" + packageid;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Delete departure information by departure id
		 */
		public function deleteDeparture(id:int, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE departure SET offlineOperator='DEL', updateTime = 'T' WHERE id=" + id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Converts array object to Departure array collection.
		 * Month in Flex starts from 0 to 11 (Jan to Dec).
		 * @param:	o:Object	database return object
		 * @return:	Departure object after conversion
		 */
		 public function typeObject(o:Object):Departure
		 {
		 	var d:Departure = new Departure();
		 	d.id = o.id;
		 	d.packageid = o.packageid;
		 	d.departureDate = o.departureDate;
		 	d.departureMonth = o.departureMonth;
		 	d.departureYear = o.departureYear;
		 	d.slot = o.slot;
		 	d.available = o.available;
		 	d.updateTime = o.updateTime;
		 	d.offlineOperator = o.offlineOperator;
		 	d.date = o.date;
		 	return d;
		 }
		 /**
		 * Converts array object to Departure array collection
		 * @param: a:Array array of objects returned by SQL
		 * @return: ArrayCollection of Photo after conversion from Array
		 */
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int = 0; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}
		/**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}
	}
}